﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;
using ADODB;
using System.Web;


namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
       List<String> CheckedNodes = new List<String>();

        public Form1()
        {
            InitializeComponent();
        }
        TreeNode nodeCardio = new TreeNode();
        TreeNode nodeEndo = new TreeNode();
        TreeNode nodeGastro = new TreeNode();
        TreeNode nodeMusco = new TreeNode();
        TreeNode nodeNerve = new TreeNode();
        TreeNode nodeNonsp = new TreeNode();
        TreeNode nodeRenal = new TreeNode();
        TreeNode nodePsych = new TreeNode();
        TreeNode nodeResp = new TreeNode();
        TreeNode nodeChRange1 = new TreeNode();
        TreeNode nodeChRange2 = new TreeNode();
        TreeNode nodeChRange3 = new TreeNode();
        TreeNode nodeChRange4 = new TreeNode();
        TreeNode nodePayRange1 = new TreeNode();
        TreeNode nodePayRange2 = new TreeNode();
        TreeNode nodePayRange3 = new TreeNode();
        TreeNode nodePayRange4 = new TreeNode();
        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'dRG_PaymentsDataSet3.city' table. You can move, or remove it, as needed.
            this.cityTableAdapter.Fill(this.dRG_PaymentsDataSet3.city);
            // TODO: This line of code loads data into the 'dRG_PaymentsDataSet2.region' table. You can move, or remove it, as needed.
            this.regionTableAdapter.Fill(this.dRG_PaymentsDataSet2.region);
            // TODO: This line of code loads data into the 'dRG_PaymentsDataSet1.state' table. You can move, or remove it, as needed.
            this.stateTableAdapter.Fill(this.dRG_PaymentsDataSet1.state);
            // TODO: This line of code loads data into the 'dRG_PaymentsDataSet.SummaryFunction' table. You can move, or remove it, as needed.
            this.summaryFunctionTableAdapter.Fill(this.dRG_PaymentsDataSet.SummaryFunction);
            // Build treeview nodes Main facets
            /* TreeNode nodeAll = new TreeNode();
            nodeAll.Text = "All Records";
            nodeAll.Checked = true;
            treeView1.ExpandAll();
            treeView1.Nodes.Add(nodeAll);           //Root node in TreeView'  */

            TreeNode nodeState = new TreeNode();
            int TotalCount = (int)summaryFunctionTableAdapter.TotalCountQuery();
            nodeState.Text = "State" + " ( " + TotalCount + " ) ";
            nodeState.Checked = true;
            nodeState.Tag = "Parent";
            treeView1.Nodes.Add(nodeState);  // State node

            TreeNode nodeOrgan = new TreeNode();
            nodeOrgan.Text = "Organ System" + " ( " + TotalCount + " ) ";
            nodeOrgan.Checked = true;
            nodeOrgan.Tag = "Parent";
            tvOrgan.Nodes.Add(nodeOrgan);  // Organ node

            TreeNode nodeCharges = new TreeNode();
            nodeCharges.Text = "Average Provider Charges" + " ( " + TotalCount + " ) ";
            nodeCharges.Checked = true;
            nodeCharges.Tag = "Parent";
            tvCharges.Nodes.Add(nodeCharges);  // Charges node

            TreeNode nodePayments = new TreeNode();
            nodePayments.Text = "Average Medicare Payments" + " ( " + TotalCount + " ) ";
            nodePayments.Checked = true;
            nodePayments.Tag = "Parent";
            tvPayments.Nodes.Add(nodePayments);  // Payments node

            // Organ system child nodes
            //TreeNode nodeCardio = new TreeNode();
            nodeCardio.Text = "Cardiovascular";
            int OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodeCardio.Text);
            nodeCardio.Text = "Cardiovascular" + " ( " + OrganSysCount + " ) ";
            nodeCardio.Checked = true;
            nodeCardio.ImageIndex = 1;
            nodeCardio.SelectedImageIndex = 1;
            nodeCardio.Tag = "Cardio";
            nodeOrgan.Nodes.Add(nodeCardio);

            //TreeNode nodeEndo = new TreeNode();
            nodeEndo.Text = "Endocrine";
            OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodeEndo.Text);
            nodeEndo.Text = "Endocrine" + " ( " + OrganSysCount + " ) ";
            nodeEndo.Checked = true;
            nodeEndo.ImageIndex = 1;
            nodeEndo.SelectedImageIndex = 1;
            nodeEndo.Tag = "Endo";
            nodeOrgan.Nodes.Add(nodeEndo);

            //TreeNode nodeGastro = new TreeNode();
            nodeGastro.Text = "Gastrointestinal";
            OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodeGastro.Text);
            nodeGastro.Text = "Gastrointestinal" + " ( " + OrganSysCount + " ) ";
            nodeGastro.Checked = true;
            nodeGastro.ImageIndex = 1;
            nodeGastro.SelectedImageIndex = 1;
            nodeGastro.Tag = "Gastro";
            nodeOrgan.Nodes.Add(nodeGastro);

            //TreeNode nodeMusco = new TreeNode();
            nodeMusco.Text = "Muscoskeletal";
            OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodeMusco.Text);
            nodeMusco.Text = "Muscoskeletal" + " ( " + OrganSysCount + " ) ";
            nodeMusco.Checked = true;
            nodeMusco.ImageIndex = 1;
            nodeMusco.SelectedImageIndex = 1;
            nodeMusco.Tag = "Musco";
            nodeOrgan.Nodes.Add(nodeMusco);

            //TreeNode nodeNerve = new TreeNode();
            nodeNerve.Text = "Nervous";
            OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodeNerve.Text);
            nodeNerve.Text = "Nervous" + " ( " + OrganSysCount + " ) ";
            nodeNerve.Checked = true;
            nodeNerve.ImageIndex = 1;
            nodeNerve.SelectedImageIndex = 1;
            nodeNerve.Tag = "Nerve";
            nodeOrgan.Nodes.Add(nodeNerve);

            //TreeNode nodeNonsp = new TreeNode();
            nodeNonsp.Text = "Non-specific";
            OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodeNonsp.Text);
            nodeNonsp.Text = "Non-specific" + " ( " + OrganSysCount + " ) ";
            nodeNonsp.Checked = true;
            nodeNonsp.ImageIndex = 1;
            nodeNonsp.SelectedImageIndex = 1;
            nodeNonsp.Tag = "Nonsp";
            nodeOrgan.Nodes.Add(nodeNonsp);

            TreeNode nodePsych = new TreeNode();
            nodePsych.Text = "Psychological";
            OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodePsych.Text);
            nodePsych.Text = "Psychological" + " ( " + OrganSysCount + " ) ";
            nodePsych.Checked = true;
            nodePsych.ImageIndex = 1;
            nodePsych.SelectedImageIndex = 1;
            nodePsych.Tag = "Psych";
            nodeOrgan.Nodes.Add(nodePsych); 

            //TreeNode nodeRenal = new TreeNode();
            nodeRenal.Text = "Renal";
            OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodeRenal.Text);
            nodeRenal.Text = "Renal" + " ( " + OrganSysCount + " ) ";
            nodeRenal.Checked = true;
            nodeRenal.ImageIndex = 1;
            nodeRenal.SelectedImageIndex = 1;
            nodeRenal.Tag = "Renal";
            nodeOrgan.Nodes.Add(nodeRenal);

            //TreeNode nodeResp = new TreeNode();
            nodeResp.Text = "Respiratory";
            OrganSysCount = (int)summaryFunctionTableAdapter.OrganCountQuery(nodeResp.Text);
            nodeResp.Text = "Respiratory" + " ( " + OrganSysCount + " ) ";
            nodeResp.Checked = true;
            nodeResp.ImageIndex = 1;
            nodeResp.SelectedImageIndex = 1;
            nodeResp.Tag = "Resp";
            nodeOrgan.Nodes.Add(nodeResp);

            //TreeNode nodeChRange1 = new TreeNode();
            int ChargesCount = (int)summaryFunctionTableAdapter.ScalarChRange1Query();
            nodeChRange1.Text = "$0 - $250,000" + " ( " + ChargesCount + " ) ";
            nodeChRange1.Checked = true;
            nodeChRange1.ImageIndex = 1;
            nodeChRange1.SelectedImageIndex = 1;
            nodeChRange1.Tag = "ChRange1";
            nodeCharges.Nodes.Add(nodeChRange1);

            //TreeNode nodeChRange2 = new TreeNode();
            ChargesCount = (int)summaryFunctionTableAdapter.ScalarChRange2Query();
            nodeChRange2.Text = "$250,001 - $500,000" + " ( " + ChargesCount + " ) ";
            nodeChRange2.Checked = true;
            nodeChRange2.ImageIndex = 1;
            nodeChRange2.SelectedImageIndex = 1;
            nodeChRange2.Tag = "ChRange2";
            nodeCharges.Nodes.Add(nodeChRange2);

            //TreeNode nodeChRange3 = new TreeNode();
            ChargesCount = (int)summaryFunctionTableAdapter.ScalarChRange3Query();
            nodeChRange3.Text = "500,001 - $750,000" + " ( " + ChargesCount + " ) ";
            nodeChRange3.Checked = true;
            nodeChRange3.ImageIndex = 1;
            nodeChRange3.SelectedImageIndex = 1;
            nodeChRange3.Tag = "ChRange3";
            nodeCharges.Nodes.Add(nodeChRange3);

            /* TreeNode nodeChRange4 = new TreeNode();
            ChargesCount = (int)summaryFunctionTableAdapter.ScalarChRange4Query();
            nodeChRange4.Text = "$750,001 - $1,000,000" + " ( " + ChargesCount + " ) ";
            nodeChRange4.Checked = true;
            nodeChRange4.ImageIndex = 1;
            nodeChRange4.SelectedImageIndex = 1;
            nodeChRange4.Tag = "ChRange4";
            nodeCharges.Nodes.Add(nodeChRange4); */

            //TreeNode nodePayRange1 = new TreeNode();
            int PaymentsCount = (int)summaryFunctionTableAdapter.ScalarPayRange1Query();
            nodePayRange1.Text = "$0 - $60,000" + " ( " + PaymentsCount + " ) ";
            nodePayRange1.Checked = true;
            nodePayRange1.ImageIndex = 1;
            nodePayRange1.SelectedImageIndex = 1;
            nodePayRange1.Tag = "PayRange1";
            nodePayments.Nodes.Add(nodePayRange1);

            //TreeNode nodePayRange2 = new TreeNode();
            PaymentsCount = (int)summaryFunctionTableAdapter.ScalarPayRange2Query();
            nodePayRange2.Text = "$60,001 - $120,000" + " ( " + PaymentsCount + " ) ";
            nodePayRange2.Checked = true;
            nodePayRange2.ImageIndex = 1;
            nodePayRange2.SelectedImageIndex = 1;
            nodePayRange2.Tag = "PayRange2";
            nodePayments.Nodes.Add(nodePayRange2);

            //TreeNode nodePayRange3 = new TreeNode();
            PaymentsCount = (int)summaryFunctionTableAdapter.ScalarPayRange3Query();
            nodePayRange3.Text = "$120,001 - $180,000" + " ( " + PaymentsCount + " ) ";
            nodePayRange3.Checked = true;
            nodePayRange3.ImageIndex = 1;
            nodePayRange3.SelectedImageIndex = 1;
            nodePayRange3.Tag = "PayRange3";
            nodePayments.Nodes.Add(nodePayRange3); 
            
            //TreeNode nodePayRange4 = new TreeNode();
            /*PaymentsCount = (int)summaryFunctionTableAdapter.ScalarPayRange4Query();
            nodePayRange4.Text = "$120,001 - $160,000" + " ( " + PaymentsCount + " ) ";
            nodePayRange4.Checked = true;
            nodePayRange4.ImageIndex = 1;
            nodePayRange4.SelectedImageIndex = 1;
            nodePayRange4.Tag = "PayRange4";
            nodePayments.Nodes.Add(nodePayRange4); */

            //nested while loops used to build child node facets for the state>region>city relationships
            int comboStateCounter = 1;
            int stateCount = (int)stateTableAdapter.ScalarCountStateQuery();
            String nodeStateText;
            String nodeRegionText;
            String nodeCityText;
            String nodeStateName;
            String nodeCityState;
            int nodeRegionID;
            int cityRegionID;

            int RegionTotalCountValue = (int)regionTableAdapter.ScalarRegionTotalCount();

            while (comboStateCounter <= stateCount)
            {
                int gridRegionCounter = 0;
                comboBoxState.SelectedIndex = comboStateCounter;
                nodeStateName = comboBoxState.Text;
                TreeNode myStateNode = new TreeNode();
                int returnStateCtValue = (int)summaryFunctionTableAdapter.ScalarStateCtQuery(nodeStateName);
                nodeStateText = nodeStateName + " (" + returnStateCtValue + " )";
                myStateNode.Text = nodeStateText;
                myStateNode.Checked = true;
                myStateNode.ImageIndex = 1;
                myStateNode.Tag = "State";
                nodeState.Nodes.Add(myStateNode);
                myStateNode.Name = nodeStateName; 
                int returnCountRegionValue = (int)regionTableAdapter.ScalarCountRegionQuery(nodeStateName);
                //Message Box for testing verified we're getting counts greater than zero
                
                    //Message box test shows the while statement is okay
                        while (gridRegionCounter < RegionTotalCountValue)
                        //Only interested in regions associated with current state
                        {
                          int gridCityCounter = 0;
                          int CityTotalCountValue = 0;
                          String Region_State = dataGridRegion.Rows[gridRegionCounter].Cells[2].Value.ToString();
                            // Message Box test of if condition worked ok
                          //Set regionID variable so it canbe used in City node building
                          nodeRegionID = Convert.ToInt32(dataGridRegion.Rows[gridRegionCounter].Cells[0].Value.ToString());
                          // Retrieve count of records in grid for the region facet
                          int returnRegionCtValue = (int)summaryFunctionTableAdapter.ScalarRegionCtQuery(nodeRegionID);
                          nodeRegionText = dataGridRegion.Rows[gridRegionCounter].Cells[1].Value.ToString() + " (" + returnRegionCtValue + " )";
                             
                          if (nodeStateName == Region_State)
                          {
                              TreeNode myRegionNode = new TreeNode(nodeRegionText);
                              // Region Node label with count that appears to user
                             // nodeRegionText = dataGridRegion.Rows[gridRegionCounter].Cells[1].Value.ToString() + " (" + returnRegionCtValue + " )";
                              myRegionNode.Text = nodeRegionText;
                              myRegionNode.Checked = true;
                              myRegionNode.ImageIndex = 2;
                              myRegionNode.SelectedImageIndex = 2;
                              myRegionNode.Tag = "Region";
                              myStateNode.Nodes.Add(myRegionNode);
                              myRegionNode.Name = Region_State + "Region" + nodeRegionID;
                              cityTableAdapter.GetData();
                             cityTableAdapter.GetDataByRegionCities(nodeRegionID);
                             CityTotalCountValue = (int)cityTableAdapter.ScalarCityTotalCount();
                              while (gridCityCounter < CityTotalCountValue)
                              {   cityRegionID = Convert.ToInt32(dataGridCity.Rows[gridCityCounter].Cells[2].Value.ToString());
                                  if (cityRegionID == nodeRegionID)
                                  { 
                                      nodeCityText = dataGridCity.Rows[gridCityCounter].Cells[0].Value.ToString();
                                      nodeCityState = dataGridCity.Rows[gridCityCounter].Cells[1].Value.ToString();
                                     // MessageBox.Show("City and GridCityCounter: " + nodeCityText + " " + gridCityCounter);
                                      int returnCityCtValue = (int)summaryFunctionTableAdapter.ScalarCityCtQuery(nodeCityText, nodeCityState);
                                      TreeNode myCityNode = new TreeNode(nodeCityText);
                                      myCityNode.Text = nodeCityText + " ( " + returnCityCtValue + " )";
                                      myCityNode.Checked = true;
                                      myCityNode.ImageIndex = 3;
                                      myCityNode.SelectedImageIndex = 3;
                                      myCityNode.Tag = "City";
                                      //myCityNode.
                                      myRegionNode.Nodes.Add(myCityNode);
                                      myCityNode.Name = nodeCityText + nodeCityState;
                                      if (gridCityCounter == CityTotalCountValue)
                                      { break; }
                                  }
                                  gridCityCounter = gridCityCounter + 1;

                              } 
                              if (gridRegionCounter == RegionTotalCountValue)
                              { break; }
                          } 
                           gridRegionCounter = gridRegionCounter + 1;
                        } 
                comboStateCounter = comboStateCounter + 1;
            }
            treeView1.ExpandAll(); 
            

        }

        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {

        }

        private void treeView1_TreeNodeCheckChanged(object sender, TreeViewEventArgs e)
        {
        }
        String sqlOrgan;
        String sqlState = "";
        String sqlRegion = "";
        String sqlCity = "";
        String sqlRefresh = "SELECT * FROM dbo.summaryFunction()";
        int payQueryNumber, chQueryNumber;
        private void Traverse(TreeNode treeNode)
        {

            String nodeTag = "";

            foreach (TreeNode node in treeNode.Nodes)
            {
                if (node.Checked == true)
                {if (node.Nodes.Count > 0)
                     {
                             //use Switch to build total SQL query. Each node has Node.Tag property set to indicate field type
                             // for WHERE clauses

                             if (node.Tag != null)
                             {
                                 nodeTag = node.Tag.ToString();
                             }
                             switch (nodeTag)
                             {
                                 case "State":
                                     if (node.Checked)
                                     {
                                         if (sqlState == "")
                                         { sqlState = node.Name; }
                                         else { sqlState = sqlState + ", " + node.Name; }
                                     }
                                     break;
                                 case "Region":
                                     if (node.Checked)
                                     {
                                         String regionText = node.Text;
                                         int splitIndex = regionText.IndexOf('(');
                                         String splitRegionText = (regionText.Remove(splitIndex).Trim());
                                         if (sqlRegion == "")
                                         {
                                             sqlRegion = splitRegionText;
                                         }
                                         else { sqlRegion = sqlRegion + ", " + splitRegionText; }
                                     }
                                     break;
                                case "City":
                                     if (node.Checked)
                                     {
                                         String cityText = node.Text;
                                         int splitIndex = cityText.IndexOf('(');
                                         String splitCityText = cityText.Remove(splitIndex).Trim();
                                         if (sqlCity == "")
                                         {
                                             sqlCity = "\"" + splitCityText + "\"";
                                         }
                                         else { sqlCity = sqlCity + ", \"" + splitCityText + "\""; }
                               
                                     } 
                                     break; 
                               
                             }
                    // If the current node has child nodes, call the CheckAllChildsNodes method recursively. 
                         this.Traverse(node);
                     } 
                } 

            //Recursive function to move through nodes in treeView control
           // int nodeCountTotal = e.Nodes.Count;
           // int nodeCounter = 0;

              //  Traverse(node.Nodes);
            }
            
        }

        private void treeView1_SelectedNodeChanged(object sender, TreeViewEventArgs e)
        {

        }
        private void buttonSearch_Click(object sender, EventArgs e)
        {
            String keywordSearch;
            keywordSearch = textBoxSearch.Text;
            if (keywordSearch != "")
            {
                keywordSearch = "%" + keywordSearch + "%";
                //sjy//summaryFunctionTableAdapter.GetDataKeywordSearch(keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch);

                summaryFunctionTableAdapter.FillByKeywordSearch(dRG_PaymentsDataSet.SummaryFunction, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch);
                int count = (int)summaryFunctionTableAdapter.ScalarCountKeywordSearchQuery(keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch);
                if (count == 0)
                    MessageBox.Show("No results found!");

                //sjy//dataGridView1.Refresh();
            }
            else
            {//Message Box PopupError
                summaryFunctionTableAdapter.FillBy(dRG_PaymentsDataSet.SummaryFunction);  
                MessageBox.Show("You didn't enter any search terms!");
                //String strMessage = "No results found.";
            }
        }
        private void textBoxSearch_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {   //Added this line so that full dataset repopulates before next search           
                String keywordSearch;
                keywordSearch = textBoxSearch.Text;
                if (keywordSearch != "")
                {
                    keywordSearch = "%" + keywordSearch + "%";
                    //sjy//summaryFunctionTableAdapter.GetDataKeywordSearch(keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch);

                    summaryFunctionTableAdapter.FillByKeywordSearch(dRG_PaymentsDataSet.SummaryFunction, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch);
                    int count = (int)summaryFunctionTableAdapter.ScalarCountKeywordSearchQuery(keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch, keywordSearch);
                    if (count == 0)
                        MessageBox.Show("No results found!");

                    //sjy//dataGridView1.Refresh();
                }
                else
                {//Message Box PopupError                
                    summaryFunctionTableAdapter.FillBy(dRG_PaymentsDataSet.SummaryFunction);

                    MessageBox.Show("You didn't enter any search terms!");
                    //String strMessage = "No results found.";
                }
            }
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void textBoxSearch_TextChanged(object sender, EventArgs e)
        {

        }

        private void fillByToolStripButton_Click(object sender, EventArgs e)
        {
            try
            {
                this.regionTableAdapter.FillBy(this.dRG_PaymentsDataSet.region);
            }
            catch (System.Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }

        }

        private void fillByToolStripButton1_Click(object sender, EventArgs e)
        {
            try
            {
                this.cityTableAdapter.FillBy(this.dRG_PaymentsDataSet.city);
            }
            catch (System.Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }

        }

        private void fillByRegionCitiesToolStripButton_Click(object sender, EventArgs e)
        {
            try
            {
                this.cityTableAdapter.FillByRegionCities(this.dRG_PaymentsDataSet.city, ((int)(System.Convert.ChangeType(city_RegionToolStripTextBox.Text, typeof(int)))));
            }
            catch (System.Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }

        }

        private void treeView1_AfterCheck(object sender, TreeViewEventArgs e)
        {
            //When TreeNode check is changed, we want 3 things to happen: 1- Build query that searches through the Treeview, 
            //sees which nodes are checked. Calling a function to do this so it can be recusrsive
           
            // The code only executes if the user caused the checked state to change. 
            if (e.Action != TreeViewAction.Unknown)
            {
                if (e.Node.Nodes.Count > 0)
                {
                    /* Calls the CheckAllChildNodes method, passing in the current 
                    Checked value of the TreeNode whose checked state changed. */
                    this.CheckAllChildNodes(e.Node, e.Node.Checked);
                }
                TreeNode node;
                node = treeView1.Nodes[0];
                //MessageBox.Show("The Current Node Level is: " + node.Level + "The Current Node Count is: " + node.Nodes.Count);
                Traverse(node);
               
                MessageBox.Show("The State SQL clause is now: " + sqlState);
                MessageBox.Show("The Region SQL clause is now: " + sqlRegion);
                executeStateSQLQuery();
                node = treeView1.Nodes[0];
                updateStateRegionCounts(node);
                updateOrganCounts();
                updateChargesCounts();
                updatePaymentsCounts();
               // MessageBox.Show("The City SQL clause is now: " + sqlCity);
            }
         
        }

        private void tvOrgan_AfterCheck(object sender, TreeViewEventArgs e)
        {
            //When TreeNode check is changed, we want 3 things to happen: 1- Build query that searches through the Treeview, 
            //sees which nodes are checked. Calling a function to do this so it can be recusrsive

            // The code only executes if the user caused the checked state to change. 
            //String nodeTag;
            sqlOrgan = "";
                if (e.Node.Nodes.Count > 0)
                {
                    /* Calls the CheckAllChildNodes method, passing in the current 
                    Checked value of the TreeNode whose checked state changed. */
                    this.CheckAllChildNodes(e.Node, e.Node.Checked);
                }
                TreeNode node;
                // = treeView1.Nodes[0];
                //MessageBox.Show("The Current Node Level is: " + node.Level + "The Current Node Count is: " + node.Nodes.Count);
                //Traverse(node);

                //MessageBox.Show("The State SQL clause is now: " + sqlState);
                //MessageBox.Show("The Region SQL clause is now: " + sqlRegion);
                tvOrganSQL();
                //tvChargesSQL();
                //tvPaymentSQL();
                executeOrganSQLQuery();
                node = treeView1.Nodes[0];
                updateStateRegionCounts(node);
                updateOrganCounts();
                updateChargesCounts();
                updatePaymentsCounts();
  
        }

        // Updates all child tree nodes recursively. 
        private void CheckAllChildNodes(TreeNode treeNode, bool nodeChecked)
        {
            foreach (TreeNode node in treeNode.Nodes)
            {
                node.Checked = nodeChecked;
                if (node.Nodes.Count > 0)
                {
                    // If the current node has child nodes, call the CheckAllChildsNodes method recursively. 
                    this.CheckAllChildNodes(node, nodeChecked);
                }
            }
        }

        private void tvOrganSQL()
        {
            
            if (nodeCardio.Checked == true)
            {
                if (sqlOrgan == "")
                {
                    sqlOrgan = "SELECT * FROM dbo.SummaryFunction() WHERE DRG_Organ_Sys IN (\'Cardiovascular\'";
                }
            }
            if (nodeEndo.Checked == true)
            {
                if (sqlOrgan == "")
                {
                    sqlOrgan = "SELECT * FROM dbo.SummaryFunction() WHERE DRG_Organ_Sys IN (\'Endocrine\'";
                }
                else { sqlOrgan = sqlOrgan + ", \'Endocrine\'"; }
            }
            if (nodeGastro.Checked == true)
            {
                if (sqlOrgan == "")
                {
                    sqlOrgan = "SELECT * FROM dbo.SummaryFunction() WHERE DRG_Organ_Sys IN (\'Gastrointestinal\'";
                }
                else { sqlOrgan = sqlOrgan + ", \'Gastrointestinal\'"; }
            }
            if (nodeMusco.Checked == true)
            {
                if (sqlOrgan == "")
                {
                    sqlOrgan = "SELECT * FROM dbo.SummaryFunction() WHERE DRG_Organ_Sys IN (\'Muscoskeletal\'";
                }
                else { sqlOrgan = sqlOrgan + ", \'Muscoskeletal\'"; }
            }
            if (nodeNerve.Checked == true)
            {
                if (sqlOrgan == "")
                {
                    sqlOrgan = "SELECT * FROM dbo.SummaryFunction() WHERE DRG_Organ_Sys IN (\'Nervous\'";
                }
                else { sqlOrgan = sqlOrgan + ", \'Nervous\'"; }
            }
            if (nodeNonsp.Checked == true)
            {
                if (sqlOrgan == "")
                {
                    sqlOrgan = "SELECT * FROM dbo.SummaryFunction() WHERE DRG_Organ_Sys IN (\'Non-specific\'";
                }
                else { sqlOrgan = sqlOrgan + ", \'Non-specific\'"; }
            }
            if (nodePsych.Checked == true)
            {
                if (sqlOrgan == "")
                {
                    sqlOrgan = "SELECT * FROM dbo.SummaryFunction() WHERE DRG_Organ_Sys IN (\'Psychological\'";
                }
                else { sqlOrgan = sqlOrgan + ", \'Psychological\'"; }
            }
            if (nodeRenal.Checked == true)
            {
                if (sqlOrgan == "")
                {
                    sqlOrgan = "SELECT * FROM dbo.SummaryFunction() WHERE DRG_Organ_Sys IN (\'Renal\'";
                }
                else { sqlOrgan = sqlOrgan + ", \'Renal\'"; }
            }
            if (nodeResp.Checked == true)
            {
                if (sqlOrgan == "")
                {
                    sqlOrgan = "SELECT * FROM dbo.SummaryFunction() WHERE DRG_Organ_Sys IN (\'Respiratory\'";
                }
                else { sqlOrgan = sqlOrgan + ", \'Respiratory\'"; }
            }
            if (sqlOrgan != "")
            { sqlOrgan = sqlOrgan + ")"; }
            MessageBox.Show("The Organ SQL clause is now: " + sqlOrgan);
        }

        private void tvPaymentSQL()
        { payQueryNumber = 0;
            
            if ((nodePayRange1.Checked == false) && (nodePayRange2.Checked == true) && (nodePayRange3.Checked == true))
            { payQueryNumber = 1;
                }
           if ((nodePayRange1.Checked == false) && (nodePayRange2.Checked == false) && (nodePayRange3.Checked == true))
            { payQueryNumber = 2;
                }
            if ((nodePayRange1.Checked == false) && (nodePayRange2.Checked == true) && (nodePayRange3.Checked == false))
            { payQueryNumber = 3;
                }
            if ((nodePayRange1.Checked == true) && (nodePayRange2.Checked == true) && (nodePayRange3.Checked == false))
            { payQueryNumber = 4;
                }
            if ((nodePayRange1.Checked == true) && (nodePayRange2.Checked == true) && (nodePayRange3.Checked == true))
            { payQueryNumber = 5;
                }
            if ((nodePayRange1.Checked == true) && (nodePayRange2.Checked == false) && (nodePayRange3.Checked == true))
            { payQueryNumber = 6;
                }

            MessageBox.Show("The Payments SQL query that will be called is number " + payQueryNumber);

        }

        private void tvChargesSQL()
        {
            chQueryNumber = 0;
            
            if ((nodeChRange1.Checked == false) && (nodeChRange2.Checked == true) && (nodeChRange3.Checked == true))
            { chQueryNumber = 1;
                }
           if ((nodeChRange1.Checked == false) && (nodeChRange2.Checked == false) && (nodeChRange3.Checked == true))
            { chQueryNumber = 2;
                }
            if ((nodeChRange1.Checked == false) && (nodeChRange2.Checked == true) && (nodeChRange3.Checked == false))
            { chQueryNumber = 3;
                }
            if ((nodeChRange1.Checked == true) && (nodeChRange2.Checked == true) && (nodeChRange3.Checked == false))
            { chQueryNumber = 4;
                }
            if ((nodeChRange1.Checked == true) && (nodeChRange2.Checked == true) && (nodeChRange3.Checked == true))
            { chQueryNumber = 5;
                }
            if ((nodeChRange1.Checked == true) && (nodeChRange2.Checked == false) && (nodeChRange3.Checked == true))
            { chQueryNumber = 6;
                }

            MessageBox.Show("The Charges SQL query that will be called is number " + chQueryNumber);
        }

        private void executeStateSQLQuery()
        {
            if (sqlState != "") 
            {summaryFunctionTableAdapter.FillByStateTree(dRG_PaymentsDataSet.SummaryFunction, sqlState, sqlRegion);
                   }
            else { MessageBox.Show("There are 0 results for the States and/or Regions you have selected."); }
        }

        private void executeOrganSQLQuery()
        {
            if (sqlOrgan != "") 
            //{summaryFunctionTableAdapter.FillByOrganTree(dRG_PaymentsDataSet.SummaryFunction, sqlOrgan);
            {
            string ConnectionPath = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DRG_Payments;Data Source=BEWTOPII";
            try
            {
                using (OleDbConnection cn = new OleDbConnection(ConnectionPath))
                using (OleDbCommand OleDbSearch = new OleDbCommand(sqlOrgan, cn))
                using (OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(OleDbSearch))
                {
                    DataSet objDataSet = new DataSet();
                    cn.Open();
                    objDataAdapter.Fill(objDataSet);
                    dataGridView1.DataSource = objDataSet;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
                MessageBox.Show(ex.StackTrace.ToString());
            }
            
            
        }
           
            else { MessageBox.Show("There are 0 results for the Organ Systems you have selected.");}
        }

        private void executePaymentsSQLQuery()
        {
            if (payQueryNumber == 0)
            {
                MessageBox.Show("There are 0 results for the Payment Ranges you have selected.");

            }
            else
            {//; 
                switch (payQueryNumber)
                {
                    case 1:
                        summaryFunctionTableAdapter.FillByPaymentsTree1(dRG_PaymentsDataSet.SummaryFunction);
                        return;
                    case 2:
                        summaryFunctionTableAdapter.FillByPaymentsTree2(dRG_PaymentsDataSet.SummaryFunction);
                        return;
                    case 3: 
                        summaryFunctionTableAdapter.FillByPaymentsTree3(dRG_PaymentsDataSet.SummaryFunction);
                        return;
                    case 4:
                        summaryFunctionTableAdapter.FillByPaymentsTree4(dRG_PaymentsDataSet.SummaryFunction);
                        return;
                    case 5:
                        summaryFunctionTableAdapter.FillByPaymentsTree5(dRG_PaymentsDataSet.SummaryFunction);
                        return;
                    case 6:
                        summaryFunctionTableAdapter.FillByPaymentsTree6(dRG_PaymentsDataSet.SummaryFunction);
                        return;
                    default:
                        return;
                }
            }
        }

        private void executeChargesSQLQuery()
        {
            if (chQueryNumber == 0)
            {
                MessageBox.Show("There are 0 results for the Charge Ranges you have selected.");

            }
            else
            {
                switch (chQueryNumber)
                {
                    case 1:
                        summaryFunctionTableAdapter.FillByChargesTree1(dRG_PaymentsDataSet.SummaryFunction);
                        return;
                    case 2:
                        summaryFunctionTableAdapter.FillByChargesTree2(dRG_PaymentsDataSet.SummaryFunction);
                        return;
                    case 3:
                        summaryFunctionTableAdapter.FillByChargesTree3(dRG_PaymentsDataSet.SummaryFunction);
                        return;
                    case 4:
                        summaryFunctionTableAdapter.FillByChargesTree4(dRG_PaymentsDataSet.SummaryFunction);
                        return;
                    case 5:
                        summaryFunctionTableAdapter.FillByChargesTree5(dRG_PaymentsDataSet.SummaryFunction);
                        return;
                    case 6:
                        summaryFunctionTableAdapter.FillByChargesTree6(dRG_PaymentsDataSet.SummaryFunction);
                        return;
                    default:
                        return;
                }
            }
        }

        private void updateStateRegionCounts(TreeNode treeNode)
        {

        }

        private void updateOrganCounts()
        {

        }

        private void updateChargesCounts()
        {

        }

        private void updatePaymentsCounts()
        {

        }

        private void treeView2_AfterSelect(object sender, TreeViewEventArgs e)
        {

        }

        private void groupBox1_Enter(object sender, EventArgs e)
        {

        }

        private void treeView1_AfterSelect_1(object sender, TreeViewEventArgs e)
        {

        }

        private void treeView3_AfterSelect(object sender, TreeViewEventArgs e)
        {

        }

        private void tvCharges_AfterSelect(object sender, TreeViewEventArgs e)
        {

        }

        private void tvCharges_AfterCheck(object sender, TreeViewEventArgs e)
        {
            if (e.Node.Nodes.Count > 0)
            {
                /* Calls the CheckAllChildNodes method, passing in the current 
                Checked value of the TreeNode whose checked state changed. */
                this.CheckAllChildNodes(e.Node, e.Node.Checked);
            }
            TreeNode node;
            //node = treeView1.Nodes[0];
            //MessageBox.Show("The Current Node Level is: " + node.Level + "The Current Node Count is: " + node.Nodes.Count);
           // Traverse(node);

            //MessageBox.Show("The State SQL clause is now: " + sqlState);
            //MessageBox.Show("The Region SQL clause is now: " + sqlRegion);
            //tvOrganSQL();
            tvChargesSQL();
            //tvPaymentSQL();
            executeChargesSQLQuery();
            node = treeView1.Nodes[0];
            updateStateRegionCounts(node);
            updateOrganCounts();
            updateChargesCounts();
            updatePaymentsCounts();
        }

        private void tvPayments_AfterCheck(object sender, TreeViewEventArgs e)
        {
            if (e.Node.Nodes.Count > 0)
            {
                /* Calls the CheckAllChildNodes method, passing in the current 
                Checked value of the TreeNode whose checked state changed. */
                this.CheckAllChildNodes(e.Node, e.Node.Checked);
            }
            TreeNode node;
            //node = treeView1.Nodes[0];
            //MessageBox.Show("The Current Node Level is: " + node.Level + "The Current Node Count is: " + node.Nodes.Count);
            //Traverse(node);

            //MessageBox.Show("The State SQL clause is now: " + sqlState);
            //MessageBox.Show("The Region SQL clause is now: " + sqlRegion);
            //tvOrganSQL();
            //tvChargesSQL();
            tvPaymentSQL();
            executePaymentsSQLQuery();
            node = treeView1.Nodes[0];
            updateStateRegionCounts(node);
            updateOrganCounts();
            updateChargesCounts();
            updatePaymentsCounts();
        }


    }
}
